package com.qy127.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JDBCUtil {
    private static final String FORNAME="com.mysql.jdbc.Driver";
    private static final String URL="jdbc:mysql://127.0.0.1:3306/libray?useUnicode=true&characterEncoding=utf8&useSSl=false";
    private static final String USER="root";
    private static final String PASSWOED="";

    /**
     *加载驱动 封装
     * 因为只用加载一次 所以用静态代码块
     */
    static {
        try {
            Class.forName(FORNAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     *创建连接 封装
     * @return
     */
    public static Connection getConn(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL,USER,PASSWOED);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //list<List> 数据 封装
    public static List<List> queryList(String sql,Object... obj){

        //创建连接
        Connection conn = getConn();
        ResultSet set=null;
        PreparedStatement ps=null;
        List<List> biglist=null;
        try {
            //预编译
            ps = conn.prepareStatement(sql);
            //参数条件赋值 当有条件时才进行循环也就是参数不为空
            if(obj!=null && obj.length!=0){
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i+1,obj[i]);
                }
            }
           //执行sql语句
            set = ps.executeQuery();
            //得到是结果集 需要遍历得到想要的结果
           biglist = new ArrayList<>();
            while (set.next()){
                // 创建list 存放每一个值 一圈循环一一行
                List<Object> list = new ArrayList<>();
                for (int i = 0; i < set.getMetaData().getColumnCount(); i++) {
                    Object o = set.getObject(i + 1);
                    list.add(o);
                }
                biglist.add(list);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(set,ps,conn);
        }
        return biglist;
    }

    //list<map>
    public static List<Map> queryMap(String sql,Object... obj){
        //创建连接
        Connection conn = getConn();
        PreparedStatement ps=null;
        ResultSet set =null;
        ArrayList<Map> biglist=null;
        try {
            //编译
            ps = conn.prepareStatement(sql);
            //条件赋值 只有数组不为空才显示
            if(obj!=null && obj.length!=0){
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i+1,obj[i]);
                }
            }
            //执行sql语句
          set = ps.executeQuery();
            biglist = new ArrayList<>();
            //获得一个结果集 对结果遍历 并将值放入到map中存储
            while (set.next()){
                //创建map 存储每个值
                HashMap<Object, Object> map = new HashMap<>();
                for (int i = 0; i < set.getMetaData().getColumnCount(); i++) {
                    map.put(set.getMetaData().getColumnName(i+1),set.getObject(i+1));
                }
                biglist.add(map);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(set,ps,conn);
        }
        return biglist;
    }

    //Dml 通用方法封装
    public static int update(String sql,Object... obj){
        //创建连接
        Connection conn = getConn();
        PreparedStatement ps=null;
        try {
            //编译
             ps = conn.prepareStatement(sql);
            //参数赋值 数组不为空且长度不为0 时执行
            if(obj!=null && obj.length!=0){
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i+1,obj[i]);
                }
            }
            //执行
            int i = ps.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(null,ps,conn);
        }
        return -1;
    }


    //获取主键
    public static int getPrimaryKey(String sql,Object... obj) {
        //创建连接
        Connection conn = getConn();
        PreparedStatement ps =null;
        ResultSet keys=null;
        try {
            //编译 并获取主键
             ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            //赋值
            if (obj!=null && obj.length!=0){
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i+1,obj[i]);
                }
            }
            //执行
            ps.executeUpdate();
            //获取主键 多个
            keys = ps.getGeneratedKeys();
            //遍历获取一个
            while (keys.next()){
                int i = keys.getInt(1);
                return i;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(keys,ps,conn);
        }
        return -1;
    }

    //参数赋值封装
    public static void setParment(Object[] obj,PreparedStatement ps){
        if (obj!=null && obj.length!=0){
            for (int i = 0; i < obj.length; i++) {
                try {
                    ps.setObject(i+1,obj[i]);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //释放资源 关闭通道 封装
    public static void closeAll(ResultSet set,PreparedStatement ps,Connection conn){
        try {
            if (set!=null){
            set.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (ps!=null){
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
